from code.data.User import User
from code.dao.config import mysql_conn, cursor

import logging
import pymysql
import traceback


def add_user(user: User):
    if user.userID is None or user.password is None:
        logging.error("UserID and password are required.")
        return False
    if user.type is None:
        logging.error("User's type is required.")
        return False
    if user.name is None:
        logging.error("User's name is required.")
        return False
    if user.email is None:
        logging.error("E-mail is required.")
    sql = "INSERT INTO user(userID, password, type, name, email, valid) VALUES ('{}', '{}', '{}', '{}', '{}', '{}')".format(
        user.userID, user.password, user.type, user.name, user.email, int(user.valid))
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Inserting user into database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    if user.valid is True:
        logging.warning("The user is valid when created.")
    logging.info("User is added with SQL: " + sql)
    return True


def search_user(user: User, search_by: str = "all", fuzzy_search: bool = False):
    users = []
    if search_by == "all":
        sql = "SELECT * FROM user"
    elif search_by == "userID":
        if user.userID is None:
            logging.error("UserID is required.")
            return None
        sql = "SELECT * FROM user WHERE userID = '{}'".format(user.userID)
    elif search_by == "type":
        if user.type is None:
            logging.error("Type is required.")
            return None
        sql = "SELECT * FROM user WHERE type = '{}'".format(user.type)
    elif search_by == "name":
        if user.name is None:
            logging.error("Name is required.")
            return None
        sql = "SELECT * FROM user WHERE name = '{}'".format(user.name)
        if fuzzy_search:
            sql = "SELECT * FROM user WHERE name LIKE '%{}%'".format(user.name)
    elif search_by == "email":
        if user.email is None:
            logging.error("Email is required.")
            return None
        sql = "SELECT * FROM user WHERE email = '{}'".format(user.email)
        if fuzzy_search:
            sql = "SELECT * FROM user WHERE email LIKE '%{}%'".format(user.email)
    elif search_by == "valid":
        sql = "SELECT * FROM user WHERE valid = {}".format(int(user.valid))
    else:
        logging.error("Attribute for search_by is not exist, use one in "
                      '["all" ,"userID", "type", "name", "email", "valid"]')
        return None
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            logging.warning("User is not exist.")
            return None
    except pymysql.Error as e:
        logging.error("Searching for user failed: " + str(e.args[1]))
        traceback.print_exc()
        return None
    for result in results:
        users.append(User(result[0], result[1], result[2], result[3], result[4], bool(result[5])))
    logging.info("Search user(s) with SQL: {}".format(sql))
    return users


def update_user(user: User, update_by: str = "userID"):
    if user.userID is None:
        logging.error("UserID is required.")
        return False
    if not __check_user_exist(user.userID):
        return False
    if update_by == "password":
        if user.password is None:
            logging.error("Password is required.")
            return False
        sql = "UPDATE user SET password = '{}' WHERE userID = '{}'".format(user.password, user.userID)
    elif update_by == "type":
        if user.type is None:
            logging.error("Type is required.")
            return False
        sql = "UPDATE user SET type = '{}' WHERE userID = '{}'".format(user.type, user.userID)
    elif update_by == "name":
        if user.name is None:
            logging.error("Name is required.")
            return False
        sql = "UPDATE user SET name = '{}' WHERE userID = '{}'".format(user.name, user.userID)
    elif update_by == "email":
        if user.email is None:
            logging.error("Email is required.")
            return False
        sql = "UPDATE user SET email = '{}' WHERE userID = '{}'".format(user.email, user.userID)
    elif update_by == "vaild":
        sql = "UPDATE user SET valid = {} WHERE userID = '{}'".format(int(user.valid), user.userID)
    else:
        logging.error("Attribute for update_by is not exist, use one in "
                      '["password", "type", "name", "email", "vaild"]')
        return False
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Update user from database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    logging.info("User is updated with SQL: {}".format(sql))
    return True


def delete_user(user: User):
    if user.userID is None:
        logging.error("UserID is required.")
        return False
    if not __check_user_exist(user.userID):
        return False
    sql = "DELETE FROM user WHERE userID = '{}'".format(user.userID)
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Delete user from database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    logging.info("User is deleted with SQL: {}".format(sql))
    return True


def __check_user_exist(userID: str):
    sql = "SELECT * FROM user WHERE userID = '{}'".format(userID)
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            logging.error("User {} is not exist".format(userID))
            return False
    except pymysql.Error as e:
        logging.error("Searching for user failed: " + str(e.args[1]))
        traceback.print_exc()
        return False
    return True


if __name__ == '__main__':
    user = User("2019300003076", "123456a", "Student", "Xu Qikai", "1179156945@qq.com", False)
    print(user.to_string())
    user2 = User("2019300003077", "a654321", "Teacher", "Xu Qikai", "CayXu@outlook.com", True)
    print(add_user(user))
    print(add_user(user2))
    user = User("2019300003077", "123456")
    user2 = User("kqx", "54321")
    user3 = User("qkx", "12345")
    print(search_user(user, "UserID"))
    users = search_user(user, "userID")
    for i in users:
        print(i.to_string())
    print(search_user(user2, "userID"))
    print(search_user(user3, "userID"))
    print(update_user(user2, "password"))
    users = search_user(User(name="Xu Qikai"), "all")
    for i in users:
        print(i.to_string())
    #print(delete_user(user))
    #print(delete_user(user2))


